--Function of checking principal existence
create or replace procedure p_check_principal_exists(
    --Principal name
    a_principal_name varchar2)
as
    --Row count
    a_row_count number;
begin
    --Searching for the principal
    select count(*) into a_row_count
    from meta_principals
    where principal_name = lower(a_principal_name);
    
    if a_row_count = 0 then
        raise_application_error(-20047,
                'A principal with the specified name was not found (principal_name = '
                || a_principal_name
                || ') (#error_code = 20047)');
    end if;
end;

--go

--Function of adding table access rights
create or replace procedure meta_add_table_rights(
    --Table id
    a_table_id raw,
    --Principal name
    a_principal_name varchar2,
    --Access type
    a_access_type number,
    --Access level
    a_access_level number)
as
    --Table name
    a_table_name varchar2(26);
    --Row count
    a_row_count number;
begin
    p_check_table_exists(a_table_id);
    p_check_principal_exists(a_principal_name);
    
    --Searching for the same table rights
    select count(*) into a_row_count
    from meta_table_acl
    where table_id = a_table_id
    and principal_name = a_principal_name
    and access_type = a_access_type;
    
    if a_row_count > 0 then
        raise_application_error(-20048,
                'A table permission with the same access type was already added (#error_code = 20048)');
    end if;
    
    a_table_name := p_get_table_name(a_table_id);
    
    if a_access_type = 1 then
        execute immediate 'grant select on ' || a_table_name || ' to ' || a_principal_name;
    elsif a_access_type = 2 then
        execute immediate 'grant insert on ' || a_table_name || ' to ' || a_principal_name;
    elsif a_access_type = 3 then
        execute immediate 'grant update on ' || a_table_name || ' to ' || a_principal_name;
    elsif a_access_type = 4 then
        execute immediate 'grant delete on ' || a_table_name || ' to ' || a_principal_name;
    end if;
    
    --Updating the metadata
    insert into meta_table_acl(table_id, principal_name, access_type, access_level)
    values(a_table_id, lower(a_principal_name), a_access_type, a_access_level);
    
    --Updating principal acl matrix
    p_update_principal_acl_matrix(a_table_id, a_principal_name);
    
end;

--go

--Function of updating principal acl matrix
create or replace procedure p_update_principal_acl_matrix(
    --Table id
    a_table_id raw,
    --Principal name
    a_principal_name varchar2)
as
    --Row count
    a_row_count number;
begin
    --Searching for principal acl
    select count(*) into a_row_count
    from meta_table_acl
    where table_id = a_table_id
    and principal_name = lower(a_principal_name);
    
    --If there is no acl for this principal
    if a_row_count = 0 then
        return;
    end if;
    
    --Updating acl for all users that are affected by the principal
    for u in (select column_value user_name from table(p_users_affected_by_principal(a_principal_name))) loop
        p_update_user_acl_matrix(a_table_id, u.user_name, a_principal_name);
    end loop;
end;

--go

--Function of updating user acl matrix for the specified principal
create or replace procedure p_update_user_acl_matrix(
    --Table id
    a_table_id raw,
    --User name
    a_user_name varchar2,
    --Principal name
    a_principal_name varchar2)
as
begin
    --Reading principal acl
    for acl in (select access_type, access_level
        from meta_table_acl
        where table_id = a_table_id
        and principal_name = lower(a_principal_name)) loop
        
        --If it is a full access right
        if acl.access_level = 6 then
            p_add_full_access_right(a_table_id, a_user_name, acl.access_type);
        --If it is a own rows access rights
        elsif acl.access_level = 1 then
            p_add_own_rows_access_right(a_table_id, a_user_name, acl.access_type);
        --If it is unit-level access
        else
            --Searching for the unit ids for the specified access type
            for u in (select column_value unit_id 
                from table(p_get_unit_ids_for_unit_right(a_user_name, acl.access_level))) loop                
                p_add_unit_access_right(a_table_id, a_user_name, u.unit_id, acl.access_type);
            end loop;        
        end if;
        
    end loop;
end;

--go

--Function of adding a unit-level access right
create or replace procedure p_add_unit_access_right(
    --Table id
    a_table_id raw,
    --User name
    a_user_name varchar2,
    --The unit id
    a_unit_id raw,
    --Access type
    a_access_type number)
as
    --Row count
    a_row_count number;
begin
    --Searching for the access rights
    select count(*) into a_row_count
    from meta_table_acl_unit_access
    where table_id = a_table_id
    and user_name = lower(a_user_name)
    and unit_id = a_unit_id
    and access_type = a_access_type;
    
    if a_row_count = 0 then
        --Inserting the access right
        insert into meta_table_acl_unit_access
        (table_id, user_name, unit_id, access_type)
        values (a_table_id, lower(a_user_name), a_unit_id, a_access_type);
    end if;
end;

--go

--Function of adding full access rights
create or replace procedure p_add_full_access_right(
    --Table id
    a_table_id raw,
    --User name
    a_user_name varchar2,
    --Access type
    a_access_type number)
as
    --Row count
    a_row_count number;
begin
    --Searching for the access rights
    select count(*) into a_row_count
    from meta_table_acl_full_access
    where table_id = a_table_id
    and user_name = lower(a_user_name)
    and access_type = a_access_type;
    
    if a_row_count = 0 then
        --Inserting access right
        insert into meta_table_acl_full_access (table_id, user_name, access_type)
        values (a_table_id, lower(a_user_name), a_access_type);
    end if;
end;  
 
--go

--Function of adding own rows access right
create or replace procedure p_add_own_rows_access_right(
    --Table id
    a_table_id raw,
    --User name
    a_user_name varchar2,
    --Access type
    a_access_type number)
as
    --Row count
    a_row_count number;
begin
    --Searching for the access rights
    select count(*) into a_row_count
    from meta_table_acl_own_rows
    where table_id = a_table_id
    and user_name = lower(a_user_name)
    and access_type = a_access_type;
    
    if a_row_count = 0 then
        --Inserting access right
        insert into meta_table_acl_own_rows (table_id, user_name, access_type)
        values (a_table_id, lower(a_user_name), a_access_type);
    end if;
end;  
 
--go

--Function of getting a list of units affected by the specified user unit-level access right
create or replace function p_get_unit_ids_for_unit_right(
    --User name
    a_user_name varchar2,
    --Access level
    a_access_level number)
return meta_id_list
as
    --Current unit id
    a_curr_unit_id raw(16);
    --The unit id list
    unit_id_list meta_id_list := new meta_id_list();
    --Local id list
    local_id_list meta_id_list := meta_id_list();
    --Current index
    a_curr_index number;
begin
  
    --Enumerating user units
    for user_unit in (select unit_id
        from meta_user_units
        where user_name = lower(a_user_name)) loop
        
        a_curr_unit_id := user_unit.unit_id;
        
        local_id_list.delete;
        
       --If scope is current unit
        if a_access_level = 2 then
            --Adding the current unit id to the list
            local_id_list.extend;
            local_id_list(local_id_list.last) := a_curr_unit_id;
        --If scope is current and child units
        elsif a_access_level = 3 then
            --Adding current and child units to the list
            local_id_list := p_get_unit_children(a_curr_unit_id);
        --If scope is current and parent units
        elsif a_access_level = 4 then
            --Adding current and parent units to the list
            local_id_list := p_get_unit_parents(a_curr_unit_id);
        --If scope is current, parent and child units
        elsif a_access_level = 5 then
            --Adding current, parent and child units to the list
            local_id_list := p_get_unit_parents_children(a_curr_unit_id);
        end if;
        
        --Copying local results
        a_curr_index := local_id_list.first;
        
        -- We are no checking unit ids for duplicates
        -- bacause it is ok
        while a_curr_index is not null loop
            unit_id_list.extend;
            unit_id_list(unit_id_list.last) := local_id_list(a_curr_index);
            
            a_curr_index := local_id_list.next(a_curr_index);
        end loop;

    end loop;
    
    return unit_id_list;
end;

--go

--Function of user affected by the principal
create or replace function p_users_affected_by_principal(
    --Principal name
    a_principal_name varchar2)
return meta_name_list
as
    --Result user list
    a_user_list meta_name_list := meta_name_list();
    --Principal type
    a_principal_type varchar(30);
begin
    
    --Reading principal type
    select principal_type into a_principal_type
    from meta_principals
    where principal_name = lower(a_principal_name);
    
    --If it is a user
    if a_principal_type = 'user' then
        a_user_list.extend;
        a_user_list(a_user_list.last) := a_principal_name;
        return a_user_list;
    end if;
    
    --If it is a role
    select distinct user_name
    bulk collect into a_user_list
    from meta_user_roles
    where container_role_name in
        --Selecting all child roles
        (select role_name
        from meta_role_roles
        start with container_role_name = lower(a_principal_name)
        connect by prior container_role_name = role_name
        union
        select lower(a_principal_name) from dual);
    
    return a_user_list;
end;

--go

--Function of deleting user acl matrix
create or replace procedure p_delete_user_acl_matrix(
    --Table id
    a_table_id raw,
    --User name
    a_user_name varchar2,
    --Access type
    a_access_type number)
as
begin
    --Deleting the unit-level access rights
    delete from meta_table_acl_unit_access
    where table_id = a_table_id
    and user_name = lower(a_user_name)
    and access_type = a_access_type;
    
    --Deleting the full access rights
    delete from meta_table_acl_full_access
    where table_id = a_table_id
    and user_name = lower(a_user_name)
    and access_type = a_access_type;
    
    --Deleting the own row access rights
    delete from meta_table_acl_own_rows
    where table_id = a_table_id
    and user_name = lower(a_user_name)
    and access_type = a_access_type;
end;

--go

--Function of refreshing user acl
create or replace procedure p_refresh_user_acl(
    --User name
    a_user_name varchar2)
as
begin
    for t in (select table_id from meta_tables) loop
        p_refresh_user_acl_matrix(t.table_id, a_user_name, 1);
        p_refresh_user_acl_matrix(t.table_id, a_user_name, 2);
        p_refresh_user_acl_matrix(t.table_id, a_user_name, 3);
        p_refresh_user_acl_matrix(t.table_id, a_user_name, 4);
    end loop;
end;

--go

--Function of refreshing users matrix acl
create or replace procedure p_refresh_user_acl_matrix(
    --Table id
    a_table_id raw,
    --User name
    a_user_name varchar2,
    --Access type
    a_access_type number)
as
    --Principal name list
    a_principal_list meta_name_list;
begin
    --Deleting old user acl matrix
    p_delete_user_acl_matrix(a_table_id, a_user_name, a_access_type);

    --Getting all user principals
    a_principal_list := p_get_user_principals(a_user_name);
    
    --Updating user acl matrix
    for i in 1..a_principal_list.count loop
        p_update_principal_acl_matrix(a_table_id, a_principal_list(i));
    end loop;
end;

--go

--Function of deleting table access rights
create or replace procedure meta_delete_table_rights(
    --Table id
    a_table_id raw,
    --Principal name
    a_principal_name varchar2,
    --Access type
    a_access_type number)
as
    --Table name
    a_table_name varchar2(26);
    --Row count
    a_row_count number;
    --Users affected by the principal
    a_affected_user_list meta_name_list;
begin
    p_check_table_exists(a_table_id);
    p_check_principal_exists(a_principal_name);
    
    --Searching for the table rights
    select count(*) into a_row_count
    from meta_table_acl
    where table_id = a_table_id
    and principal_name = a_principal_name
    and access_type = a_access_type;
    
    if a_row_count = 0 then
        raise_application_error(-20049,
                'Specified table permission was not found (#error_code = 20049)');
    end if;

    a_table_name := p_get_table_name(a_table_id);
    
     if a_access_type = 1 then
        execute immediate 'revoke select on ' || a_table_name || ' from ' || a_principal_name;
    elsif a_access_type = 2 then
        execute immediate 'revoke insert on ' || a_table_name || ' from ' || a_principal_name;
    elsif a_access_type = 3 then
        execute immediate 'revoke update on ' || a_table_name || ' from ' || a_principal_name;
    elsif a_access_type = 4 then
        execute immediate 'revoke delete on ' || a_table_name || ' from ' || a_principal_name;
    end if;
    
    --Getting affected by the principal users
    a_affected_user_list := p_users_affected_by_principal(a_principal_name);
    
    --Updating the metadata
    delete from meta_table_acl
    where table_id = a_table_id
    and principal_name = lower(a_principal_name)
    and access_type = a_access_type;
    
    --Updating users affected by the principal
    for i in 1..a_affected_user_list.count loop
        p_refresh_user_acl_matrix(a_table_id, a_affected_user_list(i), a_access_type);
    end loop;
    
end;

--go